using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class KhachHangDAO:DataProvider
    {
        public DataTable TimKhachHang(KhachHangDTO kh)
        {
            string sql = "select * from KhachHang where 1=1 ";
            if (kh.Ma != 0)
                sql += " and Ma=" + kh.Ma.ToString();
            if (kh.Ten != "")
                sql += " and Ten="+kh.Ten.ToString();
            if(kh.CMND!=0)
                sql += " and CMND=" + kh.CMND.ToString();
            if (kh.DiaChi != "")
                sql += " and DiaChi=" + kh.DiaChi.ToString();
            if(kh.MaLoai!=0)
                sql += " and MaLoai=" + kh.MaLoai.ToString();
            sql += " and Xoa=" + kh.Xoa.ToString();
            DataTable dt = DocBang(sql);
            return dt;
        }
        public void Them(KhachHangDTO khdto)
        {
            OleDbConnection cn=new OleDbConnection();
            cn = this.KetNoi();
            string str = "Insert into KhachHang(Ten, CMND, DiaChi, MaLoai) values (?, ?, ?, ?)";

            OleDbCommand cmd=new OleDbCommand(str,cn);

            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.Parameters.Add("@CMND", OleDbType.Integer);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoai", OleDbType.Integer);

            cmd.Parameters["@Ten"].Value = khdto.Ten;
            cmd.Parameters["@CMND"].Value = khdto.CMND;
            cmd.Parameters["@DiaChi"].Value = khdto.DiaChi;
            cmd.Parameters["@MaLoai"].Value = khdto.MaLoai;

            cmd.ExecuteNonQuery();

            cn.Close();
        }
        public DataTable LayMaxMa()
        {
            string sql = "select Max(Ma) from KhachHang";
            return DocBang(sql);
        }
        public void Sua(KhachHangDTO khdto)
        {
            OleDbConnection cn=new OleDbConnection();
            cn = KetNoi();
            string sql = "Update KhachHang Set Ten = ? , CMND = ? , DiaChi = ? , MaLoai = ? Where Ma = ?";
            OleDbCommand cmd=new OleDbCommand(sql,cn);            

            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.Parameters.Add("@CMND", OleDbType.Integer);
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
            cmd.Parameters.Add("@MaLoai", OleDbType.Integer);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);

            cmd.Parameters["@Ten"].Value = khdto.Ten;
            cmd.Parameters["@CMND"].Value = khdto.CMND;
            cmd.Parameters["@DiaChi"].Value = khdto.DiaChi;
            cmd.Parameters["@MaLoai"].Value = khdto.MaLoai;
            cmd.Parameters["@Ma"].Value = khdto.Ma;
            
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void Xoa(int makh)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = KetNoi();
            string sql = "Update KhachHang Set Xoa = ?  Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(sql, cn);

            cmd.Parameters.Add("@Xoa", OleDbType.Boolean);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);

            cmd.Parameters["@Xoa"].Value = true;
            cmd.Parameters["@Ma"].Value = makh;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataTable TraCuuKH(string sql)
        {
            DataTable dt = DocBang(sql);
            return dt;
        }
    }
}
